package org.light.reflecttable.daoimpl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.Set;
import java.util.TreeSet;

import org.javaforever.gatescore.utils.StringUtil;
import org.light.domain.Domain;
import org.light.domain.DomainSerialComparator;
import org.light.domain.Field;
import org.light.domain.FieldSerialComparator;
import org.light.reflecttable.dao.TableDao;
import org.light.reflecttable.domain.Table;
import org.light.reflecttable.utils.ReflectUtil;

public class TableDaoImpl implements TableDao{
	public List<Table> listAllTables(Connection connection) throws Exception{
		try {
			String query = "show tables;";
			PreparedStatement ps = connection.prepareStatement(query);
			ResultSet result = ps.executeQuery();
			ArrayList<Table> list = new ArrayList<Table>();
			long serial = 1L;
			while(result.next()) {
				//Build the list object.
				Table table = new Table();
				table.setDatabaseName("clock");
				table.setTableName(result.getString(1));
				table.setActive(true);
				table.setId(serial);
				//Build the object list.
				list.add(table);
				serial ++;
			}
			return list;
		} catch (Exception e){
			e.printStackTrace();
			return null;
		}
	}

	@Override
	public List<Table> listAllPgTables(Connection connection) throws Exception{
		try {
			String query = "SELECT tablename, description FROM pg_tables a left join pg_class c on relname=a.tablename left join pg_description d on oid=objoid and objsubid=0 WHERE schemaname = 'public';";
			PreparedStatement ps = connection.prepareStatement(query);
			ResultSet result = ps.executeQuery();
			ArrayList<Table> list = new ArrayList<Table>();
			long serial = 1L;
			while(result.next()) {
				//Build the list object.
				Table table = new Table();
				table.setDatabaseName("clock");
				table.setTableName(result.getString("tablename"));
				table.setLabel(result.getString("description"));
				table.setActive(true);
				table.setId(serial);
				//Build the object list.
				list.add(table);
				serial ++;
			}
			return list;
		} catch (Exception e){
			e.printStackTrace();
			return null;
		}
	}

	@Override
	public List<Domain> fetchDataDomains(Connection connection, Domain d, String tableName ,int topn) throws Exception {
		String query = ReflectUtil.generateSelectAllStatement(d, tableName ,topn);
		System.out.println("JerryDebug:"+query);
		PreparedStatement ps = connection.prepareStatement(query);
		ResultSet result = ps.executeQuery();
		Set<Domain> dataDomains = new TreeSet<>(new DomainSerialComparator());
		long serial = 100L;
		while(result.next()) {
			Domain dataDomain = new Domain();
			dataDomain.setPlural(d.getPlural());
			Set<Field> dataFields = new TreeSet<>(new FieldSerialComparator());
			long fieldSerial = 10L;
			for (Field f: d.getFields()) {
				SimpleDateFormat dsdf = new SimpleDateFormat("yyyy-MM-dd");
				SimpleDateFormat dtsdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
				Field ff = new Field();				
				ff.setFieldName(f.getFieldName());
				ff.setFixedName(f.getFixedName());
				ff.setFieldType(f.getFieldType());
				ff.setFieldNull(f.getFieldNull());
				ff.setFieldDefault(f.getFieldDefault());
				ff.setFieldKey(f.getFieldKey());
				ff.setFieldExtra(f.getFieldExtra());
				ff.setSerial(fieldSerial);
				if ("bool".equalsIgnoreCase(ff.getFieldType())) {
					ff.setFieldValue(""+result.getBoolean(f.getSnakeFieldName()));
				} else if ("date".equalsIgnoreCase(ff.getFieldType())) {
					if (result.getDate(f.getSnakeFieldName()) != null) {
						ff.setFieldValue(dsdf.format(result.getDate(f.getSnakeFieldName())));
					} else {
						ff.setFieldValue("null");
					}
				} else if ("datetime".equalsIgnoreCase(ff.getFieldType())) {
					if (result.getDate(f.getSnakeFieldName()) != null) {
						ff.setFieldValue(dtsdf.format(result.getDate(f.getSnakeFieldName())));
					} else {
						ff.setFieldValue("null");
					}
				} else if ("image".equalsIgnoreCase(ff.getFieldType())) {
					ff.setFieldValue("null");
				} else {
					ff.setFieldValue(result.getString(f.getSnakeFieldName()));
				}
				dataFields.add(ff);
				fieldSerial += 10L;
			}
			dataDomain.setFields(dataFields);
			dataDomain.setSerial(serial);
			dataDomains.add(dataDomain);
			serial += 100L;
		}
		return new ArrayList<>(dataDomains);
	}
	
	public String findTableComment(Connection connection, String tableName, String dbName) throws Exception{
		String query = "select table_name, table_comment from information_schema.tables where table_schema = '"+dbName+"' and table_name = '"+tableName+"';";
		//System.out.println(query);
		PreparedStatement ps = connection.prepareStatement(query);
		ResultSet result = ps.executeQuery();
		while(result.next()) {
			if (!StringUtil.isBlank(result.getString("table_comment"))) return result.getString("table_comment");
		}
		return "";
	}
	
	public String findPgTableComment(Connection connection, String tableName, String dbName) throws Exception{
		String query = "SELECT tablename, description FROM pg_tables a left join pg_class c on relname=a.tablename left join pg_description d on oid=objoid and objsubid=0 WHERE schemaname = 'public' and tablename='"+tableName+"';";
		//System.out.println(query);
		PreparedStatement ps = connection.prepareStatement(query);
		ResultSet result = ps.executeQuery();
		while(result.next()) {
			if (!StringUtil.isBlank(result.getString("description"))) return result.getString("description");
		}
		return "";
	}
}
